package com.itheima.health.util;

import com.itheima.health.pojo.OrderSetting;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * @author ：leaflei
 * @date ：Created in 2022年08月01日
 * @version: 1.0
 * @description ： 读取Excel文件
 */
@Slf4j
public class ExcelImportUtils {


    /**
     * 获取单元格各类型值，返回字符串类型
     */
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                ;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }

    /**
     * 读取Excel文件，存储到OrderSetting对象中
     */
    public static List<OrderSetting> readFile(InputStream is) {
        //创建List集合，存储OrderSetting对象
        ArrayList<OrderSetting> orderSettingsList = new ArrayList<>();
        try {
            //创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //获取第一个工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //获取当前工作表最后一行的行号，行号从0开始
            int lastRowNum = sheet.getLastRowNum();
            //遍历每一行，跳过第一行
            for (int i = 1; i <= lastRowNum; i++) {

                //根据行号获取行对象
                XSSFRow row = sheet.getRow(i);
                //获取当前行的最后一个单元格的索引号
                short lastCellNum = row.getLastCellNum();
                // row.getCell(索引) 获取指定单元格
                //遍历每一行上的每一个单元格
                //创建对象，存储数据
                OrderSetting orderSetting = new OrderSetting();
                //设置已预约人数，默认0
                orderSetting.setReservations(0);
                for (short j = 0; j < lastCellNum; j++) {
                    //跳过第一行，从第二行开始获取
                    //获取日期，并转换
                    String orderDate = getCellValueByCell(row.getCell(0));
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = simpleDateFormat.parse(orderDate);
                    //获取预约人数
                    String number = getCellValueByCell(row.getCell(1));
                    //设置预约日期
                    orderSetting.setOrderDate(date);
                    //设置总预约人数
                    orderSetting.setNumber(Integer.parseInt(number));
                }
                //把数据添加到List集合中
                orderSettingsList.add(orderSetting);
            }
            //关闭流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        //返回List集合
        return orderSettingsList;
    }
}
